<?php

class Users_Model extends CI_Model {
    
	public function __construct() {
		$this -> load -> database();
	}

	// module insert user
	function insert($data = array()) {
		$data_insert = array(
                        'name' =>$data['name'], 
                        'user_name' =>$data['userName'], 
                        'password' => md5($data['password']), 
						'email' => $data['email'],
                        'city' => $data['city'], 
                        'state' => $data['state'], 
                        'company' => $data['company'], 
                        'update_date' => time()
                    );
		$result = $this -> db -> insert('user', $data_insert);
		$insert_id = mysql_insert_id();
		if ($result) {
			$id = $this -> db -> insert_id();
			return $insert_id;
		} else
			return 0;
	}
    
    function get_user($data = array()){
        $password_encrypt = md5($data['password']);
        $this->db->select('user_name');
        $this->db->where("user_name", $data['userName']);
        $this->db->where("password", $password_encrypt);
        $query = $this->db->get('user');
			
        if ($query != NULL && $query->num_rows() > 0) {
            return true;
        }
        
        return FALSE;
    }
    // Check username exist in database
	function is_exist_username($username) {
		$this -> db -> where('user_name', $username);
		$q = $this -> db -> get('user');
		$data = $q -> result_array();
		//print_r($data);
		if (count($data) > 0)
			return true;
		else
			return false;
		
	}
	
	// Check username is requested from another user name
	function is_request_username($username, $cur_username){
		$sql = "SELECT * FROM buddy_relationship WHERE user_name2='" .$cur_username . "' and user_name1 = '" . $username . "' and state = 0";
		$query = $this->db->query($sql);
    	$data = $query -> result_array();
		if (count($data) > 0)
			return true;
		else
			return false;
	}
	// Check username and cur_username has been requested before
	function is_has_been_request_username($username, $cur_username){
		$sql = "SELECT * FROM buddy_relationship WHERE ((user_name2='" .$cur_username . "' and user_name1 = '" . $username . "') ";
		$sql .= "or (user_name1='" .$cur_username . "' and user_name2 = '" . $username . "')) ";
		$sql .= "and (state = 0 or state = 1)";
		$query = $this->db->query($sql);
    	$data = $query -> result_array();
		if (count($data) > 0)
			return true;
		else
			return false;
	}
	// Insert relation ship state
	function insert_relationship_state($username1, $username2){
		$data_insert = array(
                        'user_name1' => $username1, 
                        'user_name2' =>$username2, 
                        'state' => 0, 
                        'date_request' => time()
                    );
		$result = $this -> db -> insert('buddy_relationship', $data_insert);
		$insert_id = mysql_insert_id();
		if ($result) {
			$id = $this -> db -> insert_id();
			return $insert_id;
		} else
			return 0;
	}
	// Update relationship state
	function update_relationship_state($username, $authtoken, $state){
		if($state == 2 ){
			$sql = "DELETE FROM buddy_relationship WHERE user_name1 = '" .$username . "' and ";
			$sql .= "user_name2 = (SELECT user_name FROM auth_token WHERE auth_token = '" .$authtoken . "')";
		}else{
			$sql = "UPDATE buddy_relationship SET state =" . $state;
			$sql .= " WHERE user_name1= '" . $username ."' and ";
			$sql .= "user_name2 = (SELECT user_name FROM auth_token WHERE auth_token = '" .$authtoken . "')";
		}
		//echo $sql;
		$query = $this->db->query($sql);
		if($query != NULL){
			return true;
		} 
		return false;
	}
	// Update relationship state
	function update_relationship_sharing($username, $cur_name, $sharing){
		
		$sql = "UPDATE buddy_relationship SET sharing =" . $sharing;
		$sql .= " WHERE (user_name2 = '" . $username ."' and user_name1 = '" . $cur_name . "') or";
		$sql .= "(user_name1 = '" . $username . "' and user_name2 = '" . $cur_name . "')";
		$query = $this->db->query($sql);
		if($query != NULL){
			return true;
		} 
		return false;
	}
    // function is to search user_name from given name
    function search_friend_name($friend_name, $cur_name){
    	$sql = "SELECT user_name as userName FROM user WHERE name like". "'%" . $friend_name . "%' ";
    	$sql .= "and (user_name = (SELECT user_name1 FROM buddy_relationship WHERE state = 1 and user_name2 ='" .$cur_name ."') ";
    	$sql .= "or user_name = (SELECT user_name2 FROM buddy_relationship WHERE state = 1 and user_name1 ='" .$cur_name ."'))";
    	$query = $this->db->query($sql);
    	$data = $query -> result_array();
		return $data;
    }
    
	// function is to search user_name from given name
    function search_member_name($friend_name, $cur_name){
    	//$sql = "SELECT user_name AS userName, name AS fullName FROM user WHERE name like". "'%" . $friend_name . "%' ";
    	//$sql .= " and user_name != '" . $cur_name . "'";
    	$sql = "SELECT user_tbl.user_name, buddy_tbl.user_name1, buddy_tbl.user_name2, user_tbl.name, buddy_tbl.state, buddy_tbl.sharing FROM user as user_tbl";
    	$sql .= " left outer join (SELECT user_name1, user_name2, state, sharing FROM buddy_relationship where user_name1='" .$cur_name . "' or user_name2='". $cur_name . "') as buddy_tbl on user_name = user_name1 or user_name=user_name2";
    	$sql .= " where user_name != '" . $cur_name . "' and name like " . "'%" . $friend_name . "%'";
    	$query = $this->db->query($sql);
    	$data = $query -> result_array();
		return $data;
    }
    
	// function update buddy_relationship from response of requested user
    function update_token_user($data = array()){
        $sql = "UPDATE user SET pushToken = '".$data['pushToken']."'";
        $sql .= " WHERE user_name = '".$data['userName']."'";
        $query = $this->db->query($sql);
    }
    
    // function check auth_token
    function check_token($token){
    	if (trim($token) != '') {
        	$this->db->select('id');
            $this->db->where("auth_token", $token);
            $query = $this->db->get('auth_token');
            
         	if ($query != NULL && $query->num_rows() > 0) {
                 return $query->row_array();
            }
        }
        return FALSE;
    }
    
    // function insert auth_token
    function insert_auth_token($data){
        $result = FALSE;
        $sql = "UPDATE auth_token SET state = 0, upd_dt = '".date("Y-m-d H:i:s", time())."'";
        $sql .= " WHERE user_name = '".$data['user_name']."'";
        $sql .= " AND state = 1";
        $query = $this->db->query($sql);
        if (is_array($data)) {
            $result = $this->db->insert('auth_token', $data);
            if($result){
                return TRUE;
            }
        }
        return $result;
    }
    
    // function save or update shift
    function save_shift($decodeJsonShift, $userName){
        $result = FALSE;
        $arrDates = Array();
        $arrInsert = Array();
        $stringInsert = "";
        
        foreach($decodeJsonShift as $item){
            // push data date to array arrDates
            array_push($arrDates, "'".$item->{'date'}."'");
            
            // create string value insert
            $stringInsert = "(  '".$userName."',
                                '".$item->{'date'}."', 
                                '".$item->{'shift_range'}."',
                                '".$item->{'note'}."',
                                '".$item->{'color_id'}."'   
                            )";
            // push data to array insert
            array_push($arrInsert, $stringInsert);
        }
        
        // delete before insert
        $sqlDel = "DELETE FROM shift";
        $sqlDel .= " WHERE 
                        username = '".$userName."'
                        AND date IN (".implode(', ',$arrDates).")";
        $query = $this->db->query($sqlDel);
        
        // insert shift
        $sqlIns = "INSERT INTO shift (username, date, shift_range, note, color_id) VALUES ".implode(', ',$arrInsert);
        $query = $this->db->query($sqlIns);
    }
    
    // function update auth_token
    function update_auth_token($data){
        $sql = "UPDATE auth_token SET state = 0, upd_dt = '".date("Y-m-d H:i:s", time())."'";
        $sql .= " WHERE user_name = '".$data['userName']."'";
        $sql .= " AND auth_token = '".$data['authToken']."'";
        $query = $this->db->query($sql);
    }
    
    function check_expire_auth_token($auth_token){
        $sql = "SELECT state FROM auth_token";
        $sql .= " WHERE auth_token = '".$auth_token."'";
        $sql .= " AND state = 1";
        $query = $this->db->query($sql);
        if ($query != NULL && $query->num_rows() > 0) {
             return TRUE;
        }
        return FALSE;
    }
    // get friend request
    /*function get_friend_pending_request($cur_name){
    	$sql = "SELECT name, user_name as userName, buddy_relationship.state, sharing FROM user, buddy_relationship WHERE (user_name = user_name1 and user_name2='" . $cur_name . "') ";
    	$sql.= "or (user_name = user_name2 and user_name1='" .$cur_name . "')";
    	//echo $sql;
    	$query = $this->db->query($sql);
    	$data = $query -> result_array();
		return $data;
    }*/
	// get friend request
    function get_friend_pending_request($cur_name){
    	$sql = "SELECT name, user_name1, user_name2, buddy_relationship.state, sharing FROM user, buddy_relationship WHERE (user_name = user_name1 and user_name2='" . $cur_name . "') ";
    	$sql.= "or (user_name = user_name2 and user_name1='" .$cur_name . "')";
    	//echo $sql;
    	$query = $this->db->query($sql);
    	$data = $query -> result_array();
		return $data;
    }
    // get friend request
    function get_friend_shift($date, $cur_name){
    	$sql = "SELECT username, shift_range FROM shift WHERE date= '" . $date . "' and (username IN ";
    	$sql .= "(SELECT user_name1 FROM buddy_relationship WHERE user_name2 = '" . $cur_name . "' and state = 1) or username IN ";
    	$sql .= "(SELECT user_name2 FROM buddy_relationship WHERE user_name1 = '" . $cur_name . "' and state = 1))";
    	//echo $sql;
    	$query = $this->db->query($sql);
    	$data = $query -> result_array();
		return $data;
    }
    
    function get_my_shift($username, $date){
    	$sql = "SELECT date, shift_range, note, color_id 
                FROM shift 
                WHERE 
                    SUBSTRING(date FROM 1 FOR 6) = '".$date."'
                    AND username IN ('".$username."')";
    	//echo $sql;
    	$query = $this->db->query($sql);
    	$data = $query -> result_array();
		return $data;
    }
    
    function get_user_from_auth_token($auth_token){
    	$sql = "SELECT user_name FROM auth_token";
        $sql .= " WHERE auth_token = '".$auth_token."'";
        $query = $this->db->query($sql);
        if ($query != NULL && $query->num_rows() > 0) {
             return $query->result_array();
        }
        return '';
    }
    
    function get_push_token_by_username($user_name){
        $sql = "SELECT pushToken FROM user";
        $sql .= " WHERE user_name = '".$user_name."'";
        $query = $this->db->query($sql);
        if ($query != NULL && $query->num_rows() > 0) {
             return $query->result_array();
        }
        return '';
    }
}
